 Important: |
| Linq queries against a database, using the LinqMetaData queryable object can still contain in-memory constructs, like creating Lists, arrays, calling a method etc. Linq to LLBLGen Pro tries as much as possible to execute this code locally in-memory, if appropriate: either when the data is fetched and placed into objects, or when the query is constructed. It can be that this isn't always possible, for example if you specify a method call on an in-memory object which relies on data known only during the query execution in the database. Typically this occurs when Linq to Object queries are merged with Linq to LLBLGen Pro queries. It's recommended that you don't merge these queries, unless you're sure it will work (and not throw an exception at runtime): keep database queries separated from in-memory (Linq to Objects) queries as much as possible: not only is it more clear what's executed where, but it's also less error prone. |
 Note:
|
VB.NET users shouldn't use the Aggregate keyword, as the VB.NET compiler doesn't generate any Expression tree from the Aggregate usage in some situations due to a bug in the VB.NET compiler. Instead it will produce a query which fetches all data of the target table/query into memory and apply the aggregate function on that data. This is very inefficient. As it's unclear when the VB.NET compiler refuses to produce a proper Expression tree and when it works, it's recommended to use the extension methods to be safe. So instead of: Dim max = Aggregate o In metaData.Order Into m=Max(o.OrderDate) do: Dim max = metaData.Order.Max(Function(o) o.OrderDate) |
| Note: |
| VB.NET users should use the .Value property of Nullable(Of T) typed fields instead of just the field in filter expressions, like expressions in the Where clause. The VB.NET compiler introduces coalesce statements on these boolean expressions if it detects a Nullable(Of T) typed element in the expression which are useless and which bloat the query unnecessary. Linq to LLBLGen Pro strips off these needless coalesce expressions if it runs into one of them (except in the projection (select)), however it can be it can't make the proper decision and the query fails. Example of a proper query (o.EmployeeId is a Nullable(Of Integer) typed field) : Dim q = From o In metaData.Order _
Where o.EmployeeId.Value=2 _
Select o
Example of a query which contains the Coalesce operator around EmployeeId=2: Dim q = From o In metaData.Order _
Where o.EmployeeId=2 _
Select o
The unnecessary Coalesce expression is detected in this case, however to avoid unnecessary errors and unnecessary complex SQL, use the '.Value' property on the Nullable(Of T) typed fields. To filter on Nothing (null), it's necessary to use the field instead of the '.Value' property, though this is ok, as the VB.NET compiler doesn't wrap these expressions with a coalesce statement: Dim q = From o In metaData.Order _
Where o.EmployeeId = Nothing _
Select o
|
// per country-city the # of customers is determined.
var q = from c in metaData.Customer
group c by new { c.Country, c.City } into g
select new { g.Key, g.Count()};
' per country-city the # of customers is determined.
Dim q = From c In metaData.Customer _
Group By c.Country, c.City _
Into Count() _
Select Country, City, Count
// returns set of objects which contain per country the customers in a hierarchy
var q = from c in metaData.Customer
group c by c.Country into g
select g;
' returns set of objects which contain per country the customers in a hierarchy
Dim q = From c In metaData.Customer _
Group c By c.Country Into G = Group
|
Note:
|
| VB.NET users shouldn't append the Into grouping clause with a Select statement if they want to refer to the whole Group, like in the example above where the group is stored in variable 'G'. Doing so will wrap the nested query into a derived table, which makes it impossible to fetch it, as a nested query can only be present in the projection. |
var q = from c in metaData.Customer
orderby c.CustomerId[1]
select c;
Dim q = From c In metaData.Customer _
Order By c.CustomerId(1) _
Select c
To fetch entities mapped onto the resultset of a Table Valued Function, the LinqMetaData class offers each mapped Table Valued Function which is the target of an entity as a method. In the following example, the Table Valued Function CustomersFromCountry which returns all Customer entities for the country specified is used.
var q = from c in metaData.CustomersFromCountry("Germany")
where c.CompanyName.StartsWith("S")
select c;
Dim q = From c in metaData.CustomersFromCountry("Germany") _
Where c.CompanyName.StartsWith("S") _
Select c
The query above results in SQL like:
SELECT LPA_R1.CustomerID As CustomerId, ... FROM dbo.fn_CustomersFromCountry(@country) AS LPA_R1 WHERE LPA_R1.CompanyName LIKE 'S%'
However the parameters have to be filled in with constants: you can't specify an element of the query as parameter value: the value of a parameter has to be specified as an in-memory value, not as a value which is obtained from the database. An example query which fails:
// C#
var q = from c in metaData.Customer
where metaData.GetOrdersForCustomer(c.CustomerId).Contains(order)
select c;
// Query 1
var q = from e in metaData.Employee
let x = e as BoardMemberEntity
where x.CompanyCarID==3
select e;
// Query 2
var q = from e in metaData.Employee.Cast<BoardMemberEntity>()
where e.CompanyCarID==3
select e;
// Query 3
var q = from e in metaData.Employee.TypeOf<BoardMemberEntity>()
where e.CompanyCarID==3
select e;
' Query 1
Dim q = From e In metaData.Employee _
Let x = e As BoardMemberEntity _
Where x.CompanyCarID=3 _
Select e
' Query 2
Dim q = From e In metaData.Employee.Cast(Of BoardMemberEntity)() _
Where e.CompanyCarID=3 _
Select e
' Query 3
Dim q = From e In metaData.Employee.TypeOf(Of BoardMemberEntity)()
Where e.CompanyCarID=3 _
Select e
// as in a filter, supported. Fetches all BoardMemberEntity instances
var q = from e in metaData.Employee
where (e as BoardMemberEntity) != null
select e;
// as in a projection. Not supported.
var q = from e in metaData.Employee
select new { SomeField = e as BoardMemberEntity};
' As in a filter, supported. Fetches all BoardMemberEntity instances
Dim q = From e In metaData.Employee _
Where Not (e As BoardMemberEntity) Is Nothing _
Select e
' As in a projection. Not supported.
Dim q = From e In metaData.Employee _
Select SomeField = e As BoardMemberEntity
// Query 1, simple entity check in entity list
var q = from c in metaData.Customer
where c.Orders.Where(o=>o.EmployeeId==3).Contains(order)
select c;
// Query 2, operand is entity which is result of query
var q = from c in metaData.Customer
where c.Orders.Contains(
(from o in metaData.Order
where o.EmployeeId == 2 select o).First())
select c;
// Query 3, operand and source are both queries.
var q = from c in metaData.Customer
where c.Orders.Where(o => o.EmployeeId == 2).Contains(
(from o in metaData.Order
where o.EmployeeId == 2 select o).First())
select c;
// Query 4, constant compare with value from query. Yes this is different.
var q = from c in metaData.Customer
where c.Orders.Where(o => o.EmployeeId > 3).Select(o => o.ShipVia).Contains(2)
select c;
// Query 5, check if a constant tuple is in the result of a query
var q = from c in metaData.Customer
where c.Orders.Select(oc => new { EID = oc.EmployeeId, CID = oc.CustomerId }).Contains(
new { EID = (int?)1, CID = "CHOPS" })
select c;
// Query 6, as 5 but now compare with a tuple created with a query
var q = from c in metaData.Customer
where c.Orders.Select(oc => new { EID = oc.EmployeeId, CID = oc.CustomerId }).Contains(
(from o in metaData.Order where o.CustomerId == "CHOPS"
select new { EID = o.EmployeeId, CID = o.CustomerId }).First())
select c;
// Query 7, checking if the value of a field in an entity is in a list of constants
List<string> countries = new List<string>() { "USA", "UK" };
var q = from c in metaData.Customer
where countries.Contains(c.Country)
select c;
// Query 8, as 7 but now with an IEnumerable
LinkedList<string> countries = new LinkedList<string>(new string[] { "USA", "UK"});
var q = from c in metaData.Customer
where countries.Contains(c.Country)
select c;
// Query 9, combination of 2 queries where the first is merged with the second and
// only the second is executed. (this is one of the reasons why you have to write
// your own Funcletizer code.
var q1 = (from c in metaData.Customer
select c.Country).Distinct();
var q2 = from c in metaData.Customer
where q1.Contains(c.Country)
select c;
// Query 10, as 7 but now with an array obtained from another array.
string[][] countries = new string[1][] { new string[] { "USA", "UK" } };
var q = from c in metaData.Customer
where countries[0].Contains(c.Country)
select c;
// Query 11, complex contains query with comparison of in-memory object list
List<Pair<string, string>> countryCities = new List<Pair<string, string>>();
countryCities.Add(new Pair<string, string>("USA", "Portland"));
countryCities.Add(new Pair<string, string>("Brazil", "Sao Paulo"));
// now fetch all customers which have a tuple of country/city in the list of countryCities.
var q = from c in metaData.Customer
where countryCities.Contains(
(from c2 in metaData.Customer
where c2.CustomerId == c.CustomerId
select new Pair<string, string>()
{ Value1 = c2.Country, Value2 = c2.City }).First())
select c;
' Query 1, simple entity check In entity list
Dim q = From c In metaData.Customer _
Where c.Orders.Where(Function(o) o.EmployeeId.Value=3).Contains(order) _
Select c
' Query 2, operand is entity which is result of query
Dim q = From c In metaData.Customer _
Where c.Orders.Contains( _
(From o In metaData.Order _
Where o.EmployeeId.Value = 2 Select o).First()) _
Select c
' Query 3, operand and source are both queries.
Dim q = From c In metaData.Customer _
Where c.Orders.Where(Function(o) o.EmployeeId.Value = 2).Contains( _
(From o In metaData.Order _
Where o.EmployeeId = 2 Select o).First()) _
Select c
' Query 4, constant compare with value From query. Yes this is different.
Dim q = From c In metaData.Customer _
Where c.Orders.Where(Function(o) o.EmployeeId.Value > 3).Select(Function(o) o.ShipVia).Contains(2) _
Select c
' Query 5, check if a constant tuple is In the result of a query
Dim q = From c In metaData.Customer _
Where c.Orders.Select(Function(oc) New With {.EID = oc.EmployeeId, .CID = oc.CustomerId}).Contains( _
New With { .EID = CType(1, Nullable(Of integer)), .CID = "CHOPS" }) _
Select c
' Query 6, as 5 but now compare with a tuple created with a query
Dim q = From c In metaData.Customer
Where c.Orders.Select(Function(oc) New With { ,EID = oc.EmployeeId, .CID = oc.CustomerId }).Contains( _
(From o In metaData.Order Where o.CustomerId = "CHOPS" _
Select New With {.EID = o.EmployeeId, .CID = o.CustomerId }).First()) _
Select c
' Query 7, checking if the value of a field In an entity is In a list of constants
Dim countries As New List(Of String)()
countries.Add("USA")
countries.Add("UK")
Dim q = From c In metaData.Customer _
Where countries.Contains(c.Country) _
Select c
' Query 8, as 7 but now with an IEnumerable
Dim countryValues(2) As String
countryValues(0)="USA"
countryValues(1)="UK"
Dim countries As new LinkedList(Of String)(countryValues)
Dim q = From c In metaData.Customer _
Where countries.Contains(c.Country) _
Select c
' Query 9, combination of 2 queries where the first is merged with the second and
' only the second is executed. (this is one of the reasons why you have to write
' your own Funcletizer code.
Dim q1 = (From c In metaData.Customer _
Select c.Country).Distinct()
Dim q2 = From c In metaData.Customer _
Where q1.Contains(c.Country) _
Select c
' Query 10, as 7 but now with an array obtained from another array.
Dim countries(0)() As String
Dim countryValues(1) As String
countryValues(0)="USA"
countryValues(1)="UK"
countries(0)=countryValues
Dim q = From c In metaData.Customer _
Where countries(0).Contains(c.Country) _
Select c
' Query 11, complex Contains query with comparison of In-memory object list
Dim countryCities As New List(Of Pair(Of String, String))()
countryCities.Add(new Pair(Of String, String)("USA", "Portland"))
countryCities.Add(new Pair(Of String, String)("Brazil", "Sao Paulo"))
' now fetch all customers which have a tuple of country/city In the list of countryCities.
Dim q = From c In metaData.Customer _
Where countryCities.Contains( _
(From c2 In metaData.Customer _
Where c2.CustomerId = c.CustomerId _
Select New Pair(Of String, String)() With _
{ .Value1 = c2.Country, .Value2 = c2.City }).First()) _
Select c
var q = (from c in metaData.Customer orderby c.CustomerId select c).TakePage(2, 20);
Dim q = (From c In metaData.Customer Order By c.CustomerId Ascending Select c).TakePage(2, 20)
// metaData is a LinqMetaData instance
metaData.ContextToUse = new Context();
// this query will use the context set
var q = from c in metaData.Customer
where ...
select ...
// resetting
metaData.ContextToUse = null;
// this query won't use the context previously set, as the metaData's property
// is null.
var q = from o in metaData.Order
...
' metaData is a LinqMetaData instance
metaData.ContextToUse = New Context()
' this query will use the context set
Dim q = From c In metaData.Customer _
Where ... _
Select ...
' resetting
metaData.ContextToUse = Nothing
' this query won't use the context previously set, as the metaData's property
' is null.
Dim q = From o In metaData.Order _
...
// exclude Photo and Notes from employee fetch
var q = (from e in metaData.Employee
select e).ExcludeFields(e=>e.Photo, e=>e.Notes);
' exclude Photo and Notes from employee fetch
Dim q = (From e In metaData.Employee _
Select e).ExcludeFields(Function(e) e.Photo, Function(e) e.Notes)
var q = from c in metaData.Customer
where c.Country=="USA"
select new
{
Name = c.ContactName,
Orders = from o in metaData.Order
where o.CustomerId == c.CustomerId
select o
};
Dim q = From c In metaData.Customer _
Where c.Country="USA" _
Select New With _
{ _
.Name = c.ContactName, _
.Orders = From o In metaData.Order _
Where o.CustomerId = c.CustomerId _
select o _
}
var q = from c in metaData.Customer
where c.Country=="USA"
select new
{
Name = c.ContactName,
Orders = c.Orders
};
Dim q = From c In metaData.Customer _
Where c.Country="USA" _
Select New With _
{ _
.Name = c.ContactName, _
.Orders = c.Orders _
}
/// Utility class which obtains value from a webservice
public class InMemoryCallExample
{
public static int GetInterestRate(string customerId)
{
return MyService.GetInterestRate(customerId);
}
}
// this class can now be used in the following query:
var q = from o in metaData.Order
select new
{
o.OrderId,
InterestRate = InMemoryCallExample.GetInterestRate(o.CustomerId)
};
''' Utility class which obtains value from a webservice
Public Class InMemoryCallExample
Public Shared Function GetInterestRate(customerId As String) As Integer
Return MyService.GetInterestRate(customerId)
End Function
End Class
' this class can now be used in the following query:
Dim q = From o In metaData.Order _
Select New With _
{
.OrderId = o.OrderId, _
.InterestRate = InMemoryCallExample.GetInterestRate(o.CustomerId) _
}
// function which applies substring call on input Func<string, string> stringChopper = s=>s.Substring(0, 3);
// this function can now be used in a query: var q = from c in metaData.Customer select stringChopper(c.CompanyName);
' function which applies substring call on input Dim stringChopper As Func(Of String, String) = Function(s) s.Substring(0, 3)
' this function can now be used in a query: Dim q = From c in metaData.Customer _ Select stringChopper(c.CompanyName)